Move Method Example

This example uses the Move method to position the record pointer based on user input.

Sub MoveX()

    Dim dbsNorthwind As Database
    Dim rstSuppliers As Recordset
    Dim varBookmark As Variant
    Dim strCommand As String
    Dim lngMove As Long

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set rstSuppliers = _
        dbsNorthwind.OpenRecordset("SELECT CompanyName, " & _
        "City, Country FROM Suppliers ORDER BY CompanyName", _
        dbOpenDynaset)

    With rstSuppliers
        ' Populate recordset.
        .MoveLast
        .MoveFirst

        Do While True
            ' Display information about current record and ask 
            ' how many records to move.
            strCommand = InputBox( _
                "Record " & (.AbsolutePosition + 1) & " of " & _
                .RecordCount & vbCr & "Company: " & _
                !CompanyName & vbCr & "Location: " & !City & _
                ", " & !Country & vbCr & vbCr & _
                "Enter number of records to Move " & _
                "(positive or negative).")

            If strCommand = "" Then Exit Do

            ' Store bookmark in case the Move doesn't work.
            varBookmark = .Bookmark

            ' Move method requires parameter of data type Long.
            lngMove = CLng(strCommand)
            .Move lngMove

            ' Trap for BOF or EOF.
            If .BOF Then
                MsgBox "Too far backward! " & _
                    "Returning to current record."
                .Bookmark = varBookmark
            End If
            If .EOF Then
                MsgBox "Too far forward! " & _
                    "Returning to current record."
                .Bookmark = varBookmark
            End If
        Loop
        .Close
    End With

    dbsNorthwind.Close

End Sub